﻿//=====================================================================
//
// File: FilestreamHttpService.MediaService     
//
// Summary: Reads data from SQL Server FILESTREAM and streams to client 
//   via HTTP.
//
// Date: November 11, 2007
//
// Authors:
//   Roger Doherty - http://blogs.msdn.com/rdoherty/
//   Zach Skyles Owens - http://blogs.msdn.com/zowens/
//
//---------------------------------------------------------------------
//
// This file is part of the Microsoft SQL Server Community Samples.
// Copyright (C) Microsoft Corporation.  All rights reserved.
//
// This source code is intended only as a supplement to Microsoft
// Development Tools and/or on-line documentation.  See these other
// materials for detailed information regarding Microsoft code samples.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
//
//===================================================================== 
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.Win32.SafeHandles;
using SqlFilestream;

namespace FilestreamHttpService
{
    public partial class MediaService : System.Web.UI.Page
    {
        // Sets the buffer size for reading and writing blobs at NTFS blocksize 512K.         
        const int blockSize = 1024 * 512; 

        protected void Page_Load(object sender, EventArgs e)
        {
            // Get query string parameter.
            string fileName = Request.QueryString["fileName"];

            #region Database connection
            // Open a connection to the database.
            SqlConnectionStringBuilder cxnString = new SqlConnectionStringBuilder();
            cxnString["Data Source"] = "(local)";
            cxnString["Integrated Security"] = true;
            cxnString["Initial Catalog"] = "FileStreamWpfHttp";
            SqlConnection cxn = new SqlConnection(cxnString.ConnectionString);
            cxn.Open();
            #endregion Database connection

            // Start a transaction
            SqlTransaction txn = cxn.BeginTransaction();

            // Build select statment and pass params.
            SqlCommand cmd = new SqlCommand("SELECT [file].PathName(), [contentType], GET_FILESTREAM_TRANSACTION_CONTEXT() FROM dbo.media WHERE fileName = @fileName;", cxn, txn);
            cmd.Parameters.Add("@fileName", SqlDbType.VarChar).Value = fileName;

            // Execute query and read results.
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
            rdr.Read();
            SqlString sqlFilePath = rdr.GetSqlString(0);
            SqlString contentType = rdr.GetSqlString(1);
            SqlBinary transactionToken = rdr.GetSqlBinary(2);
            rdr.Close();

            // Open file handle to media stored in SQL Server.
            SafeFileHandle handle = SqlNativeClient.OpenSqlFilestream(
                    sqlFilePath.Value,
                    SqlNativeClient.DESIRED_ACCESS_READ,
                    0,
                    transactionToken.Value,
                    (UInt32)transactionToken.Value.Length,
                    new SqlNativeClient.LARGE_INTEGER_SQL(0));
            FileStream fs = new FileStream(handle, FileAccess.Read);

            // Read in the file and send to client.
            byte[] buffer = new byte[blockSize];
            int bytesRead;
            Response.Buffer = false;
            Response.ContentType = contentType.Value;
            while ((bytesRead = fs.Read(buffer, 0, buffer.Length)) > 0)
            {
                Response.OutputStream.Write(buffer, 0, bytesRead);
                Response.Flush(); // Set breakpoint and stepthrough to validate media starts playing once buffer is full
            }

            #region Connection cleanup
            // Cleanup file, transaction and connection handles.
            fs.Close();
            handle.Close();
            txn.Commit();
            cxn.Close();
            #endregion Connection cleanup
        }
    }
}